package com.ultrapower.task;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ultrapower.model.Enterprise;

/**
 * @author Fanhj
 */
public class UploadEnterprise {
	
	public static final String Prefix = "企业数据服务—天眼查";
	public static Map<String, File> allFiles = new HashMap<String, File>();
	
	public static final String DB_IP = "127.0.0.1";
	public static final String DB_PORT = "3306";
	public static final String DB_USER = "root";
	public static final String DB_PSWORD = "sz199014";
	public static final String DB_NAME = "enterprisestrategy";
	public static final String DB_TABLE_NAME = "enterprise_online";
	
	public static final String[] OUT_TITLE = {"省份","地市","行业","公司名称","状态","法人","注册资本","成立日期","联系电话","地址","企业网址","企业邮箱","经验范围"};     //导出Excel的表头
	
	public void doUpload(String dir){
		File parentDir = new File(dir);
		doUpload(parentDir);
	}
	
	public void doUpload(File parentDir){
		if (parentDir!=null && parentDir.exists()) {
			if (parentDir.isDirectory()) {	//指定上传目录
				File[] allFilesArray =  parentDir.listFiles();
				if (allFilesArray != null && allFilesArray.length>0) {
					File tarFile = null;
					for (int i = 0; i < allFilesArray.length; i++) {
						if (allFilesArray[i].isFile() && allFilesArray[i].getName().equals("爬虫天眼查-黑龙江.xlsx")) {
							tarFile = allFilesArray[i];
							System.out.println("################# : " + tarFile.getName()+"            "+tarFile.getAbsolutePath());
						}else if(allFilesArray[i].isFile() && allFilesArray[i].getName().startsWith(Prefix)){
							allFiles.put(allFilesArray[i].getName(), allFilesArray[i]);
							System.out.println("$$$$$$$$$$$$$$$$$$$  : " + allFilesArray[i].getName()+"            "+allFilesArray[i].getAbsolutePath());
						}else{
							allFiles.put(allFilesArray[i].getName(), allFilesArray[i]);
						}
					}
					if(tarFile != null && tarFile.exists()){			//索引文件存在，则基于索引文件遍历整个目录上传			
						readTargetFile(tarFile);
					}else{		//如果索引文件不存在，则直接遍历里面符合条件的文件
						for (String fileName : allFiles.keySet()) {
							readFile(allFiles.get(fileName));
						}
					}
				}
			}else if (parentDir.getName().endsWith(".xls") || parentDir.getName().endsWith(".xlsx") || parentDir.getName().endsWith(".csv")) {//直接指定需要上传的导出文件
				readFile(parentDir);
			}
		}else {
			System.out.println("指定的目录/文件为找不到，请检查路径！");
		}
	}
	
	private void readTargetFile(File tarFile){
		FileInputStream inStream = null;
		try {
			long start = System.currentTimeMillis();
			System.out.println("索引文件开始读取：" + new Timestamp(start));
			inStream = new FileInputStream(tarFile);
			Workbook workBook = WorkbookFactory.create(inStream);
			Sheet sheet = workBook.getSheetAt(0);
			List<Map<String, String>> mapData = getSheetData(sheet);
			String excelName = null;
			int fileInNum = 0;
			for (Map<String, String> rowMap : mapData) {
				excelName = rowMap.get("导出的文件名（导出文件最多5000记录）");
				if(excelName !=null && !excelName.isEmpty() && allFiles.containsKey(excelName+".xlsx")){//记录中有并且文件存在则进行数据导入
					readFile(rowMap , allFiles.get(excelName+".xlsx"));
					fileInNum++;
				}
			}
			long end = System.currentTimeMillis();
			System.out.println("全部文件导入结束：" + new Timestamp(end) + "     总计导入文件数：" + fileInNum + "   耗时：" + (end-start) + "毫秒 （"+(end-start)/1000+"）秒");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取excel的sheet数据，默认第一行为表头行
	 */
	private List<Map<String, String>> getSheetData(Sheet sheet) throws Exception {
		return getSheetData(sheet , 0);//每张表格默认第一行为表头行
	}

	private List<Map<String, String>> getSheetData(Sheet sheet , int titleStartNum) throws Exception {
		List<List<String>> listData = new ArrayList<List<String>>();//sheet的所有cell数据
		List<Map<String, String>> mapData = new ArrayList<Map<String, String>>();//sheet所有表头列对应的数据
	    List<String> columnHeaderList = new ArrayList<String>();//sheet的表头列（第一行）数据
	    int numOfRows = sheet.getLastRowNum() + 1;
	    Row row = null;	//行记录
	    Map<String, String> rowMap = null;//每一行中表头对应的cell数据
	    List<String> list = null;//每一行中所有的cell数据
	    Cell cell = null;//单元格数据
	    String cellStr = null;//单元格数据的字符串形式
	    List<CellRangeAddress> listCombineCell = getCombineCell(sheet);//所有合并的单元格
	    for (int i = titleStartNum; i < numOfRows; i++) {
	        row = sheet.getRow(i);
	        rowMap = new HashMap<String, String>();
	        list = new ArrayList<String>();
	        if (row != null) {
	            for (int j = 0; j < row.getLastCellNum(); j++) {
	                cell = row.getCell(j);
	                if (cell != null) {						
	                	cellStr = getCombineCell(listCombineCell, cell, sheet).trim();
					}else {
						cellStr = "";
//						System.out.println("row : " + i +  "    col : " + j + "          cellValue  :  "+ cellStr);
					}
	                if (i == titleStartNum) {
	                    columnHeaderList.add(cellStr);
	                } else {
	                	rowMap.put(columnHeaderList.get(j), cellStr);
	                }
	                list.add(cellStr);
	            }
	        }
	        if (i > titleStartNum) {
	            mapData.add(rowMap);
	        }
	        listData.add(list);
	    }
	    return mapData;
	}
	
	/**
	 * 获取excel中某一个单元格的数据
	 */
	private String getCellValue(Cell cell) {
	    String cellValue = "";
	    DataFormatter formatter = new DataFormatter();
	    if (cell != null) {
	        switch (cell.getCellTypeEnum()) {
	            case NUMERIC:
	                if (DateUtil.isCellDateFormatted(cell)) {
	                    cellValue = formatter.formatCellValue(cell);
	                } else {
	                    double value = cell.getNumericCellValue();
	                    int intValue = (int) value;
	                    cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
	                }
	                break;
	            case STRING:
	                cellValue = cell.getStringCellValue();
	                break;
	            case BOOLEAN:
	                cellValue = String.valueOf(cell.getBooleanCellValue());
	                break;
	            case FORMULA:
	                cellValue = String.valueOf(cell.getCellFormula());
	                break;
	            case BLANK:
	                cellValue = "";
	                break;
	            case ERROR:
	                cellValue = "";
	                break;
	            default:
	                cellValue = cell.toString().trim();
	                break;
	        }
	    }
	    return cellValue.trim();
	}
	
    /**
     * 合并单元格处理,获取合并行
     */
	private  List<CellRangeAddress> getCombineCell(Sheet sheet){
        List<CellRangeAddress> list = new ArrayList<>();
        int sheetmergerCount = sheet.getNumMergedRegions(); 	//获得一个 sheet 中合并单元格的数量
        for(int i = 0; i<sheetmergerCount;i++) {  	//遍历所有的合并单元格
            list.add(sheet.getMergedRegion(i));		//获得合并单元格保存进list中
        }
        return list;
    }
    
    /**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     */
	private  String getCombineCell(List<CellRangeAddress> listCombineCell , Cell cell , Sheet sheet)throws Exception{
		String cellValue = null;
		if (cell != null) {
			int firstC = 0;
			int lastC = 0;
			int firstR = 0;
			int lastR = 0;
			cellValue = getCellValue(cell);
			for(CellRangeAddress ca : listCombineCell){
				//获得合并单元格的起始行, 结束行, 起始列, 结束列
				firstC = ca.getFirstColumn();
				lastC = ca.getLastColumn();
				firstR = ca.getFirstRow();
				lastR = ca.getLastRow();
				if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
					if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC){
						Row fRow = sheet.getRow(firstR);
						Cell fCell = fRow.getCell(firstC);
						cellValue = getCellValue(fCell);
						break;
					}
				}
			}
		} 
//        System.out.println("row : " + cell.getRowIndex() +  "    col : " + cell.getColumnIndex() + "          cellValue  :  "+ cellValue.trim());
        return cellValue;
    }

	public void readFile(File file){
		Map<String, String> rowMap = new HashMap<String, String>();
		readFile(rowMap, file);
	}
	
	private void readFile(Map<String, String> typeMap, File file) {
		FileInputStream inStream = null;
		try {
			long start = System.currentTimeMillis();
			System.out.println("单个文件开始读取：" + file.getAbsolutePath() + "         " + new Timestamp(start));
			
		    inStream = new FileInputStream(file);
		    Workbook workBook = WorkbookFactory.create(inStream);
		    Sheet sheet = workBook.getSheetAt(0);
		    List<Map<String, String>> mapData = getSheetData(sheet  , 0);
		    importToDB(typeMap, mapData);
		    
		    long end = System.currentTimeMillis();
			System.out.println("单个文件导入结束：" + file.getAbsolutePath() + "         "  + new Timestamp(end)  + "   耗时：" + (end-start) + "毫秒 （"+(end-start)/1000+"）秒");
		} catch (Exception e) {
		    e.printStackTrace();
		} finally {
		    try {
		        if (inStream != null) {
		            inStream.close();
		        }
		    } catch (IOException e) {
		        e.printStackTrace();
		    }
		}
	}
	
	private void importToDB(Map<String, String> rowMap , List<Map<String, String>> mapData){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://"+DB_IP+":"+DB_PORT+"/"+DB_NAME+"?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=utf-8&useServerPrepStmts=false&rewriteBatchedStatements=true", DB_USER, DB_PSWORD);
			conn.setAutoCommit(false);//关闭自动提交，进行手动提交
			String sql = "insert into " + DB_TABLE_NAME + " (province,city,name,industry,state,legal,registered,establishment,telephone,address,website,email,scope,develop,ent_type,create_time)  values(?,?,?,?,?,?,?,?,?,?,?,?,?,2,0,?)";
			PreparedStatement preStmt = conn.prepareStatement(sql);
			Map<String, String> rowData = null;
			for (int i = 0; i < mapData.size(); i++) {
				rowData = mapData.get(i);
				preStmt.setString(1, rowData.get("省份"));
				preStmt.setString(2, rowData.get("地市"));
				preStmt.setString(3, rowData.get("公司名称"));
				preStmt.setString(4, rowData.get("行业"));
				preStmt.setString(5, rowData.get("状态"));
				preStmt.setString(6, rowData.get("法定代表人"));
				preStmt.setString(7, rowData.get("注册资本"));
				preStmt.setString(8, rowData.get("成立日期"));
				preStmt.setString(9, rowData.get("联系电话"));
				preStmt.setString(10, rowData.get("地址"));
				preStmt.setString(11, rowData.get("企业网址"));
				preStmt.setString(12, rowData.get("邮箱"));
				preStmt.setString(13, rowData.get("经营范围"));
				preStmt.setTimestamp(14, new java.sql.Timestamp(new Date().getTime()));
				preStmt.addBatch();
				if ((i + 1) % 2000 == 0) {// 加入批量处理  
					preStmt.executeBatch();
					conn.commit();
					preStmt.clearBatch();
				}
			}
			preStmt.executeBatch();
			conn.commit();
			
			preStmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
    /**
     * @param sheetName
     * @param title
     * @param values
     * @param wb  2007格式的Excel
     * @return
     */
    public XSSFWorkbook getXSSFWorkbook(String sheetName,String []title,String [][]values, XSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件(2003-xls : HSSFWorkbook,  2007-xlsx : XSSFWorkbook)
        if(wb == null) wb = new XSSFWorkbook();

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        XSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle center = wb.createCellStyle();
        center.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        XSSFCellStyle left = wb.createCellStyle();
        left.setAlignment(HorizontalAlignment.LEFT); // 创建一个居中格式

        //声明列对象   //创建标题
        XSSFCell cell = null;
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(center);
        }

        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }
	
    /**
     * @param sheetName
     * @param title
     * @param values
     * @param wb  2003格式的Excel
     * @return
     */
    public HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件(2003-xls : HSSFWorkbook,  2007-xlsx : XSSFWorkbook)
        if(wb == null) wb = new HSSFWorkbook();

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle center = wb.createCellStyle();
        center.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        HSSFCellStyle left = wb.createCellStyle();
        left.setAlignment(HorizontalAlignment.LEFT); // 创建一个居中格式

        //声明列对象   //创建标题
        HSSFCell cell = null;
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(center);
        }

        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }
	
	/**
	 * @param filePath 下载文件的绝对路径
	 * @param cityName 
	 * @param industry
	 * @return
	 */
	public File createDownloadExcel(String filePath, String [][]content){
		File downFile = new File(filePath);
		if(downFile.exists()) downFile.delete();
		try {
			downFile.createNewFile();
			if(downFile.exists()){
				//excel标题
				
				String sheetName = "企业信息表";
				Workbook wb = null;
				if(filePath.trim().toLowerCase().endsWith(".xls")){
					wb = getHSSFWorkbook(sheetName, OUT_TITLE, content, null);//2003 - xls
				}else if(filePath.trim().toLowerCase().endsWith(".xlsx")){
					wb = getXSSFWorkbook(sheetName, OUT_TITLE, content, null);//2007 - xlsx
				}
				
				FileOutputStream os = new FileOutputStream(downFile);
				wb.write(os);
				os.flush();
				os.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return downFile;
	}
	
	/**
	 * @param filePath 下载文件的绝对路径
	 * @param enterprises  企业数据List
	 * @return
	 */
	public File createDownloadExcel(String filePath, List<Enterprise> enterprises) {
		Enterprise enterprise = null;
		String[][] content = new String[enterprises.size()][];
		for (int i = 0; i < enterprises.size(); i++) {
			content[i] = new String[OUT_TITLE.length];
			enterprise = enterprises.get(i);
			content[i][0] = enterprise.getProvince();
            content[i][1] = enterprise.getCityName();
            content[i][2] = enterprise.getIndustry();
            content[i][3] = enterprise.getCompanyName();
            content[i][4] = enterprise.getState();
            content[i][5] = enterprise.getLegal();
            content[i][6] = enterprise.getRegistered();
            content[i][7] = enterprise.getEstablishment();
            content[i][8] = enterprise.getTelephone();
            content[i][9] = enterprise.getAddress();
            content[i][10] = enterprise.getWebsite();
            content[i][11] = enterprise.getEmail();
            content[i][12] = enterprise.getScope();
		}
		return createDownloadExcel(filePath, content);
	}
	
	public static void main(String[] args) {
//		new UploadEnterprise().doUpload("C:\\Users\\Fanhj\\Desktop\\爬虫天眼查-黑龙江");
		new UploadEnterprise().createDownloadExcel("D:\\enterprise.xlsx", new String[][]{});
	}
}
